/**************************************************************************
	Replication do-file: "Corruption in Customs"
	Cyril Chalendard, Ana Fernandes, Gael Raballand and Bob Rijkers
	
	Created on: 01/07/2022
**************************************************************************/

clear all
set more off, perm
cap log close
cls

* ----------------------- DIRECTORIES AND FOLDERS ----------------------- *

* Directories in which data are stored
global main "PUT YOUR DIRECTORY PATH HERE"
cd "$folder"

* Folders
global outputdata = "$main\Output Data"
cap mkdir "$main\Tables"
global tables = "$main\Tables"

* ----------------------------- BEGINS HERE ----------------------------- *

* -------
* Dataset
* -------
use "$outputdata\CFRR.dta", clear // open dataset

* Sample
drop if sig_c_f==. // drop unnecessary observations


* --------
* Table A3
* --------

* Globals
global coreriskvars "risk tax_rate red_i mixed_dec differentiated valitrade_advice"
global addvars "ln_iwei ln_portwei ln_ival_usd nup_gap_i_int htrl_i_int e20"
global dep "ln_time fraud c1_dlog_val c1_dlog_tax htrl_f_int"
global other "adj_gapportwei adj_htrl_f_int_portwei htrl_f_ext adj_htrl_f_ext_portwei htrl_f_adv"

* Matrix
forvalues i = 1(2)3 {
	mat tableA3_`i' = J(34,2,.)
}
forvalues i = 2(2)4 {
	mat tableA3_`i' = J(34,1,.)
}

* Loop for estimations
** Excess interaction shares
local i = 1
local j = 1
foreach x of varlist eis_f eis_nr_f {
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 0 // capture summary statistics
	mat tableA3_1[`i',`j'] = `r(mean)'
	mat tableA3_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 1 // capture summary statistics
	mat tableA3_3[`i',`j'] = `r(mean)'
	mat tableA3_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Risk characteristics
local i = 6
local j = 1
foreach x of varlist $coreriskvars {
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 0 // capture summary statistics
	mat tableA3_1[`i',`j'] = `r(mean)'
	mat tableA3_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 1 // capture summary statistics
	mat tableA3_3[`i',`j'] = `r(mean)'
	mat tableA3_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Additional characteristics
local i = 14
local j = 1
foreach x of varlist $addvars {
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 0 // capture summary statistics
	mat tableA3_1[`i',`j'] = `r(mean)'
	mat tableA3_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 1 // capture summary statistics
	mat tableA3_3[`i',`j'] = `r(mean)'
	mat tableA3_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Main outcomes
local i = 23
local j = 1
foreach x of varlist $dep {
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 0 // capture summary statistics
	mat tableA3_1[`i',`j'] = `r(mean)'
	mat tableA3_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 1 // capture summary statistics
	mat tableA3_3[`i',`j'] = `r(mean)'
	mat tableA3_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}
** Additional outcomes
local i = 30
local j = 1
foreach x of varlist $other {
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 0 // capture summary statistics
	mat tableA3_1[`i',`j'] = `r(mean)'
	mat tableA3_2[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_1[`i',`j'] = `r(sd)'
	local j = `j' - 1
	
	qui: sum `x' if sample_reg==1 & eis_f!=. & sig_c_f == 1 // capture summary statistics
	mat tableA3_3[`i',`j'] = `r(mean)'
	mat tableA3_4[`i',`j'] = `r(N)'
	local j = `j' + 1
	mat tableA3_3[`i',`j'] = `r(sd)'
	local i = `i' + 1
	local j = `j' - 1
}


* Export
* ------
putexcel set "$tables\Table A3.xlsx", replace sheet(TableA3) // create a new excel spreadsheet

* Main Title
putexcel (A1:G1), merge hcenter vcenter
putexcel A1 = "Before delegated randomization of inspector assignment"

* Secondary titles
putexcel (B2:D2), merge hcenter vcenter bold
putexcel B2 = "Other declarations"
putexcel (E2:G2), merge hcenter vcenter bold
putexcel E2 = "Excess interaction declarations (corruption suspected)"
local cells `" "B" "C" "D" "E" "F" "G" "'
local labels `" "Average" "Std. dev." "Obs." "Average" "Std. dev." "Obs." "'
forvalues i = 1(1)6 {

	local x: word `i' of `labels'
	local y: word `i' of `cells'

	putexcel `y'3 = "`x'", hcenter vcenter bold	
}

* Group of dependend variable's name
local cells `" "5" "9" "26" "'
local labels `" "Excess interaction" "Controls" "Customs outcomes" "'
forvalues i = 1(1)3 {
	
	local x: word `i' of `labels'
	local y: word `i' of `cells'
	
	putexcel (A`y':G`y'), merge hcenter vcenter bold
	putexcel A`y' = "`x'", hcenter vcenter bold
}
** Excess interaction
local k = 6
local labels `" "Excess interaction share" "Excess interaction share - inspector logits" "'
forvalues i = 1(1)2 {
	
	local x: word `i' of `labels'
	
	putexcel A`k' = "`x'", left vcenter	
	local k = `k'+1
}
** Controls
local k = 10
local labels `" "Risk characteristics" "Risk score" "Tax rate" "Red channel dummy" "Mixed shipment dummy" "Differentiated share" "Valuation advice dummy" "'
forvalues i = 1(1)7 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "10" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter	
	}
	
	local k = `k'+1
}
local k = 18
local labels `" "Additional characteristics" "Log initial weight" "Log port authority weight" "Log initial value" "Log initial unit price (relative to internal prices)" "Initial hypothetical tax revenue losses (internal prices)" "High potential tax yield dummy" "'
forvalues i = 1(1)7 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "18" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter	
	}
	
	local k = `k'+1
}
** Customs outcomes
local k = 27
local labels `" "Main outcomes" "Time" "Fraud" "\Delta log value" "\Delta log tax" "Hypothetical tax revenue losses (internal prices)" "'
forvalues i = 1(1)6 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "27" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter	
	}
	
	local k = `k'+1
}
local k = 34
local labels `" "Additional outcomes" "Weight gap (port authority weight)" "Hypothetical tax revenue losses (internal prices, port authority weight)" "Hypothetical tax revenue losses (external reference prices)" "Hypothetical tax revenue losses (external reference prices, port authority weight)" "Hypothetical tax revenue losses (valuation advice)" "'
forvalues i = 1(1)6 {
	
	local x: word `i' of `labels'
	
	if "`k'" == "34" {
		putexcel A`k' = "`x'", left vcenter underline
	}
	
	else {
		putexcel A`k' = "`x'", left vcenter	
	}
	
	local k = `k'+1
}

* Coefficients
local cells `" "B" "D" "E" "G" "'
forvalues i = 1(1)4 {
	
	local x: word `i' of `cells'
	
	if "`i'" == "1" | "`i'" == "3" {
		putexcel `x'6 = matrix(tableA3_`i'), nformat(0.00) hcenter vcenter
	}
	
	else {
		putexcel `x'6 = matrix(tableA3_`i'), nformat(number_sep) hcenter vcenter
	}
}


* -------------------------------- ENDS HERE -------------------------------- *